from django.http import HttpResponse, HttpResponseRedirect
from django.shortcuts import render, render_to_response
from django.conf import settings
from django.views.decorators.csrf import csrf_exempt
from quanquaner.models.BaseModels import *
from django.db import connections
import requests, json, xlrd, qrcode, time, base64, io, datetime
from utils.wechat import Wechat
from PIL import Image

def salses_top(request):
    cursor = connections['app_db'].cursor()
    sql = 'SELECT COUNT(t.id), t.source_id FROM bq_qhtech_coin_glide t ' \
		+ 'LEFT JOIN bq_qhtech_proxy_team_user t1 ON t.source_id = t1.user_id ' \
		+ 'LEFT JOIN bq_user_info t4 ON t1.user_id = t4.user_id ' \
		+ 'LEFT JOIN bq_team t2 ON t1.team_id = t2.id ' \
		+ 'LEFT JOIN bq_qhtech_proxy t3 ON t1.proxy_id = t3.id ' \
		+ 'LEFT JOIN bq_promotion t5 ON t3.id = t5.proxy_id ' \
		+ 'LEFT JOIN bq_admin_users t6 ON t6.id = t5.admin_user_id ' \
		+ 'LEFT JOIN bq_user t7 on t1.user_id=t7.id ' \
		+ 'LEFT JOIN bq_tencent_helper_order t8 on t.glide_sn=t8.order_sn ' \
        + 'group by t.source_id ' \
        + 'order by COUNT(t.id) desc ' \
        + 'limit 20'
    cursor.execute(sql)
    r = cursor.fetchall()
    result = []
    for i in r:
        userId = str(i[1])
        cursor.execute('SELECT SUM(qhtech_coin) FROM bq_qhtech_coin_glide WHERE pay_type=1 AND source_id ='+ userId)
        qh_coin = int(cursor.fetchone()[0])
        cursor.execute('select phone from bq_user where id=' + userId)
        mobile = str(cursor.fetchone()[0])
        cursor.execute('select longitude,latitude from bq_model_daily_live where user_id= ' + userId)
        weizhi = cursor.fetchone()
        if weizhi != None:
            r = requests.get('http://api.map.baidu.com/geocoder/v2/?location={},{}&output=json&ak=E822388fd07382213217995bf88acbb3'.format(weizhi[1], weizhi[0]))
            area_dic = eval(r.text)
            area = area_dic['result']['addressComponent']['province'] + area_dic['result']['addressComponent']['city']
        else:
            area = ""
        data = SalsesTop(
            userId=i[1],
            qh_coin=qh_coin,
            order_num=i[0],
            mobile=mobile,
            area=area
        )
        result.append(data)
    SalsesTop.objects.bulk_create(result)
    return HttpResponse("")

def qqDataUpload(request):
    return render(request, 'qq_data.html', {
        "title":"同步助手数据导入",
        "site_title":"全圈数据后台",
        "site_header":"全圈数据后台",
    })

def qqDataUploadAction(request):
    data = request.FILES['qqdata'].read().decode('utf-8').replace('\n', '').replace('\r', '')
    for i in eval(data):
        qq = qqData(**i)
        qq.save()
    return HttpResponseRedirect('/admin/quanquaner/qqdata/')

def BaiduCodeUpload(request):
    return render(request, 'baidu_code.html', {
        "title":"百度推广码导入",
        "site_title":"全圈数据后台",
        "site_header":"全圈数据后台",
    })

def BaiduCodeUploadAction(request):
    excel = request.FILES['baiduexcel'].read()
    sheet = xlrd.open_workbook(filename=None, file_contents=excel)
    baidu = sheet.sheet_by_index(0)
    data = []
    code_list = BaiduCode.objects.values_list('mobile', flat=True)
    for i in range(baidu.nrows):
        print(i)
        if i == 0:
            continue
        elif str(int(baidu.row_values(i)[6])) in code_list:
            continue
        obj = BaiduCode(
            baidu_url = baidu.row_values(i)[7],
            haokan_url = baidu.row_values(i)[8],
            user_name = baidu.row_values(i)[5],
            mobile = int(baidu.row_values(i)[6])
        )
        data.append(obj)
    BaiduCode.objects.bulk_create(data)
    return HttpResponseRedirect('/admin/quanquaner/baiducode/')

def baiduShow(request, mobile):
    try:
        baidu = BaiduCode.objects.get(mobile=mobile)
        baidu_im = qrcode.make(baidu.baidu_url)
        baidu_img = baidu_im.resize((150, 150))
        haokan_im = qrcode.make(baidu.haokan_url)
        haokan_img = haokan_im.resize((150, 150))
        baidu_output = io.BytesIO()
        haokan_output = io.BytesIO()
        baidu_img.save(baidu_output, format="PNG")
        haokan_img.save(haokan_output, format="PNG")
        qrbaidu = base64.b64encode(baidu_output.getvalue()).decode('utf-8')
        qrhaokan = base64.b64encode(haokan_output.getvalue()).decode('utf-8')
        #更新一下状态
        if baidu.status == 1:
            baidu.status = 2
        elif baidu.status == 2:
            baidu.click += 1
        elif baidu.status == 0:
            return render_to_response('baidu_error.html')
        baidu.save()
        return render_to_response('baidu_show.html', {'qrbaidu': qrbaidu, 'qrhaokan': qrhaokan})
    except Exception as e:
        print(e)
        return render_to_response('baidu_error.html')

def baiduLxShow(request):
    return render(request, 'baidulx.html')

def baiduWechatShow(request, mobile):
    code = BaiduCode.objects.filter(mobile=mobile)
    if code.count() > 0:
        news = News.objects.get(id=1)
        data = {
            'title': news.title,
            'description': news.description,
            'PicUrl': news.media.media_url,
            'url': news.Url + mobile
        }
        return data
    else:
        return False

def uploadMedia(request, id):
    try:
        media = Material.objects.get(id=id)
        if media.media_id.strip() == '' or media.media_id == None:
            wechat = Wechat()
            file = {'media': open(settings.MEDIA_ROOT + media.media.url.replace('/media', ''), 'rb')}
            res = wechat.createMedia(file, 'image')
            media.media_id = res['media_id']
            media.media_url = res['url']
            media.save()
        return HttpResponseRedirect('/admin/quanquaner/material/')
    except Exception as e:
        print(e)
        return HttpResponse('System Error!')

# def findQrcode(mobile):
#     try:
#         baidu = BaiduCode.objects.filter(mobile=mobile).all()
#         if baidu.count() < 1:
#             return False
#         result = []
#         for i in baidu:
#             img_path = '/baidu/' + i.qudao_num + '.jpg'
#             if i.status == 0:
#                 im = qrcode.make(i.url)
#                 im.save(settings.MEDIA_ROOT + img_path)
#             result.append({'app_type':i.app_type, 'qrcode': settings.MEDIA_URL + img_path})
#         return result
#     except Exception as e:
#         print(e)
#         return False

def BaiduDataUpload(request):
    return render(request, 'baidu_data_upload.html', {
        "title":"百度推广数据导入",
        "site_title":"全圈数据后台",
        "site_header":"全圈数据后台",
    })

def BaiduDataUploadAction(request):
    excel = request.FILES['baiduexcel'].read()
    sheet = xlrd.open_workbook(filename=None, file_contents=excel)
    data = sheet.sheet_by_index(0)
    mobile_list = []
    order_list = []
    mobile_str = ''
    for i in range(data.nrows):
        if i == 0:
            continue
        mobile = str(int(data.row_values(i)[3]))
        order = BaiduOrderData(
            baidu = BaiduCode.objects.get(mobile=mobile),
            #phone_type = int(data.row_values(i)[4]),
            app_name = data.row_values(i)[4],
            #下面两个值有可能为空，空的话是str类型，不为空的话是float
            valid_num = data.row_values(i)[5] if isinstance(data.row_values(i)[5], float) else 0,
            yuzhuang_valid_num = data.row_values(i)[6] if isinstance(data.row_values(i)[6], float) else 0,
            date = datetime.datetime.strptime(request.POST['date'],'%Y/%m/%d')
        )
        if mobile not in mobile_list:
            mobile_list.append(mobile)
            mobile_str += '"' + mobile + '",'
        order_list.append(order)
    sql = 'SELECT ' \
        + 'a.id,' \
        + 'b.`name` 用户名字,' \
        + 'a.phone 全圈账号,' \
        + 'h.`name` 门店名称,' \
        + 'e.`name` 商户名称,' \
        + 'g.`proxy_name` 代理名称,' \
        + 'z.`proxy_name` 公司代理名称,' \
        + 'zzz.`name` 推广经理名称,' \
        + 'FROM_UNIXTIME( a.create_time, "%Y-%m-%d" ) 注册时间 ' \
        + 'FROM ' \
        + 'bq_user a ' \
        + 'LEFT JOIN bq_user_info b ON a.id = b.user_id ' \
        + 'LEFT JOIN bq_qhtech_proxy_team_user d ON a.id = d.user_id ' \
        + 'LEFT JOIN bq_team e ON e.id = d.team_id ' \
        + 'LEFT JOIN bq_user_team_info f ON f.user_id = a.id ' \
        + 'LEFT JOIN bq_team_department h ON h.id = f.department_id ' \
        + 'LEFT JOIN bq_qhtech_proxy g ON ( d.`proxy_id` = g.`id`) ' \
        + 'LEFT JOIN bq_qhtech_proxy z ON ( z.`id` = g.`big_proxy_id`) ' \
        + 'LEFT JOIN `bq_promotion` zz ON ( zz.`proxy_id` = g.`id`) ' \
        + 'LEFT JOIN `bq_admin_users` zzz ON ( zzz.`id` = zz.`admin_user_id`) ' \
        + 'WHERE ' \
        + '1 = 1 ' \
        + 'and a.phone in ({}) ' \
        + 'GROUP BY ' \
        + 'a.id'
    cursor = connections['app_db'].cursor()
    sql = sql.replace('{}', mobile_str[:-1])
    cursor.execute(sql)
    r = cursor.fetchall()
    user_info = {}
    for j in r:
        user_info[j[2]] = {
            'id': j[0],
            'user_name': j[1],
            'store': j[3],
            'team': j[4],
            'proxy': j[5],
            'bigproxy': j[6],
            'admin_user': j[7],
        }
    result = []
    for k in order_list:
        k.team = user_info[k.baidu.mobile]['team']
        k.admin_user = user_info[k.baidu.mobile]['admin_user']
        if user_info[k.baidu.mobile]['bigproxy'] == None:
            k.proxy = user_info[k.baidu.mobile]['proxy']
        else:
            k.proxy = user_info[k.baidu.mobile]['bigproxy']
        result.append(k)
    BaiduOrderData.objects.bulk_create(result)
    return HttpResponseRedirect('/admin/quanquaner/baiduorderdata/')

# def updateBaiduOrderData(request):
#     data = BaiduOrderData.objects.all()
#     try:
#         for i in data:
#             code = BaiduCode.objects.filter(qudao_num=i.qudao_num).first()
#             i.team = code.team
#             i.proxy = code.proxy
#             i.admin_user = code.admin_user
#             i.save()
#         return HttpResponse('ok')
#     except:
#         return HttpResponse('error')

def getTmallCodeImg(request, mobile):
    try:
        tmall_sn = TmallStoreSn.objects.get(store_sn=mobile)
        tmall_qrcode = qrcode.make("https://mo.m.tmall.com/union/union-offline-h5?from=ty&type=shop&pid=" + tmall_sn.pid)
        tmall_sn.click_num += 1
        tmall_sn.save()
        tmall_im = tmall_qrcode.resize((946, 946))
        img_bg = Image.open(settings.MEDIA_ROOT + '/tmall_code.png')
        img_bg.paste(tmall_im, (414, 844))
        output = io.BytesIO()
        img_bg.save(output, format="PNG")
        qrtmall = base64.b64encode(output.getvalue()).decode('utf-8')
        return render_to_response("tmall_code_show.html", {'qrtmall': qrtmall})
    except Exception as e:
        print(e)
        return HttpResponse("编码未找到，请确认")

def addTmallInfo(request):
    sheet = xlrd.open_workbook(settings.MEDIA_ROOT + '/tmall_store.xlsx')
    tmall = sheet.sheet_by_index(1)
    data = []
    for i in range(tmall.nrows):
        if i == 0 or tmall.row_values(i)[1] == '':
            continue
        sn = str(tmall.row_values(i)[1])[:5]
        try:
            code = TmallCode.objects.get(store_sn=sn)
            obj = TmallStoreSn(
                store_id = int(tmall.row_values(i)[0]),
                pid = code.pid,
                store_sn = sn
            )
            data.append(obj)
        except Exception as e:
            print(sn+"不存在")
    TmallStoreSn.objects.bulk_create(data)
    return HttpResponse(len(data))

# def addTmallInfo(request):
#     sheet = xlrd.open_workbook(settings.MEDIA_ROOT + '/tmall_store.xlsx')
#     tmall = sheet.sheet_by_index(0)
#     data = []
#     for i in range(tmall.nrows):
#         if i == 0:
#             continue
#         mobile = str(tmall.row_values(i)[2])
#         obj = TmallStore(
#             eid = tmall.row_values(i)[0],
#             mobile = mobile[:11],
#             user = tmall.row_values(i)[6],
#             province = tmall.row_values(i)[7],
#             city = tmall.row_values(i)[8],
#             district = tmall.row_values(i)[9],
#             address = tmall.row_values(i)[10],
#             store = tmall.row_values(i)[11],
#         )
#         data.append(obj)
#     TmallStore.objects.bulk_create(data)
#     return HttpResponse(len(data))